﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using FarPoint.Web.Spread;
using System.Drawing;
using System.Data.OleDb;
using System.Data;
using FarPoint.Web.Chart;

namespace FillReportOnline
{
    public partial class chart : System.Web.UI.Page
    {
        static string connection = System.Configuration.ConfigurationManager.ConnectionStrings["datasource"].ToString();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                return;
            }

            if (Session["skin"] != null)
            {
                int skin = (int)(Session["skin"]);
                FarPoint.Web.Spread.DefaultSkins.GetAt(skin).Apply(FpSpread1.Sheets[0]);
                this.FpSpread1.Sheets[0].GridLines = GridLines.None;
            }

            AddSparkLine();
            SetChart();
            this.FpSpread1.SaveChanges();
        }

        private void AddDdata()
        {
            OleDbConnection dbConnection = new OleDbConnection(connection);
            if (dbConnection == null)
                return;
            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }
            OleDbCommand dbcommand = new OleDbCommand("", dbConnection);

            string[] pros = new string[5] { "ActiveReports", "Spread", "ComponentOne Studio", "MultiRow", "ActiveAnalysis" };
            string[] salers = new string[5] { "严明磊", "张洪军", "苏礼杰", "金宏强", "卫金博" };
            string[] regions = new string[4] { "华东区", "南方区", "北方区", "西部区" };


            Random rddate = new Random(1);

            Random rdmonth = new Random(1);

            Random rdpro = new Random();

            Random rdsaler = new Random();

            Random rdregion = new Random();

            for (int i = 411; i < 511; i++)
            {
                //dbcommand.CommandText = string.Format("insert into SalesData (日期,产品,数量,收入,区域,销售经理,备注) values(#{0}#,'{1}','{2}','{3}','{4}','{5}','{6}')",
                //    "2012-" + rdmonth.Next(1, 12).ToString() + "-" + rddate.Next(1, 28).ToString(), pros[rdpro.Next(0, 4)], rdpro.Next(0, 4),"65940", regions[rdregion.Next(0, 3)], salers[rdsaler.Next(0, 4)], "暂无备注");
            }
        }

        private void AddSparkLine()
        {
            this.FpSpread1.Sheets[0].ColumnCount = 10;
            this.FpSpread1.Sheets[0].RowCount = 50;
            this.FpSpread1.Sheets[0].PageSize = 50;

            OleDbConnection dbConnection = new OleDbConnection(connection);
            if (dbConnection == null)
                return;
            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }

            //按年分配
            OleDbCommand command = new OleDbCommand("", dbConnection);

            //Componet
            command.CommandText = "select SUM(收入) from SalesData where 产品='ComponentOne Studio' and 日期 between #2012-01-01# and #2013-01-01#";
            double c109 = (double)command.ExecuteScalar();
            command.CommandText = "select SUM(收入) from SalesData where 产品='ComponentOne Studio' and 日期 between #2013-01-01# and #2014-01-01#";
            double c110 = (double)command.ExecuteScalar();

            //Spread
            command.CommandText = "select SUM(收入) from SalesData where 产品='Spread' and 日期 between #2012-01-01# and #2013-01-01#";
            double sp09 = (double)command.ExecuteScalar();
            command.CommandText = "select SUM(收入) from SalesData where 产品='Spread' and 日期 between #2013-01-01# and #2014-01-01#";
            double sp10 = (double)command.ExecuteScalar();

            //MultiRow
            command.CommandText = "select SUM(收入) from SalesData where 产品='MultiRow' and 日期 between #2012-01-01# and #2013-01-01#";
            double mt09 = (double)command.ExecuteScalar();
            command.CommandText = "select SUM(收入) from SalesData where 产品='MultiRow' and 日期 between #2013-01-01# and #2014-01-01#";
            double mt10 = (double)command.ExecuteScalar();

            //AR
            command.CommandText = "select SUM(收入) from SalesData where 产品='ActiveReports' and 日期 between #2012-01-01# and #2013-01-01#";
            double ar09 = (double)command.ExecuteScalar();
            command.CommandText = "select SUM(收入) from SalesData where 产品='ActiveReports' and 日期 between #2013-01-01# and #2014-01-01#";
            double ar10 = (double)command.ExecuteScalar();

            this.FpSpread1.Sheets[0].Cells[0, 0].Text = "品名";
            this.FpSpread1.Sheets[0].Cells[0, 1].Text = "2012年(￥)";
            this.FpSpread1.Sheets[0].Cells[0, 2].Text = "2013年(￥)";
            this.FpSpread1.Sheets[0].Cells[0, 3].Text = "合计(￥)";
            this.FpSpread1.Sheets[0].Cells[0, 4].Text = "柱状波形图";
            this.FpSpread1.Sheets[0].Cells[0, 5].Text = "折线波形图";

            this.FpSpread1.Sheets[0].Cells[1, 0].Text = "ComponentOne Studio";
            this.FpSpread1.Sheets[0].Cells[2, 0].Text = "Spread";
            this.FpSpread1.Sheets[0].Cells[3, 0].Text = "MultiRow";
            this.FpSpread1.Sheets[0].Cells[4, 0].Text = "ActiveReports";
            this.FpSpread1.Sheets[0].Cells[5, 0].Text = "合计";

            this.FpSpread1.Sheets[0].Cells[1, 1].Value = c109;
            this.FpSpread1.Sheets[0].Cells[1, 2].Value = c110;
            this.FpSpread1.Sheets[0].Cells[2, 1].Value = sp09;
            this.FpSpread1.Sheets[0].Cells[2, 2].Value = sp10;
            this.FpSpread1.Sheets[0].Cells[3, 1].Value = mt09;
            this.FpSpread1.Sheets[0].Cells[3, 2].Value = mt10;
            this.FpSpread1.Sheets[0].Cells[4, 1].Value = ar09;
            this.FpSpread1.Sheets[0].Cells[4, 2].Value = ar10;

            this.FpSpread1.Sheets[0].Cells[1, 3].Formula = "SUM(B2:C1)";
            this.FpSpread1.Sheets[0].Cells[2, 3].Formula = "SUM(B3:C2)";
            this.FpSpread1.Sheets[0].Cells[3, 3].Formula = "SUM(B4:C3)";
            this.FpSpread1.Sheets[0].Cells[4, 3].Formula = "SUM(B5:C5)";
            this.FpSpread1.Sheets[0].Cells[5, 1].Formula = "SUM(B2:B5)";
            this.FpSpread1.Sheets[0].Cells[5, 2].Formula = "SUM(C2:C5)";
            this.FpSpread1.Sheets[0].Cells[5, 3].Formula = "SUM(D2:D5)";

            SheetView sv = FpSpread1.Sheets[0];

            ExcelSparklineSetting setting1 = new ExcelSparklineSetting();
            setting1.MarkersColor = Color.Brown;
            setting1.ShowHigh = true;
            setting1.ShowLow = true;
            setting1.ShowMarkers = true;
            setting1.HighMarkerColor = Color.Orange;
            setting1.LowMarkerColor = Color.Red;
            setting1.LastMarkerColor = Color.SpringGreen;
            setting1.NegativeColor = Color.Red;

            ExcelSparkline columnSparkline0 = new ExcelSparkline(1, 4, "Sheet1!B2:D2");
            ExcelSparkline columnSparkline1 = new ExcelSparkline(2, 4, "Sheet1!B3:D3");
            ExcelSparkline columnSparkline2 = new ExcelSparkline(3, 4, "Sheet1!B4:D4");
            ExcelSparkline columnSparkline3 = new ExcelSparkline(4, 4, "Sheet1!B5:D5");
            ExcelSparkline columnSparkline4 = new ExcelSparkline(5, 4, "Sheet1!B6:D6");
            //2. Create a Sparkline Group with an ExcelSparklineSetting and Sparkline type
            ExcelSparklineGroup groupColumn = new ExcelSparklineGroup(setting1, SparklineType.Column);
            //3. Add Sparkline to group
            groupColumn.Add(columnSparkline0);
            groupColumn.Add(columnSparkline1);
            groupColumn.Add(columnSparkline2);
            groupColumn.Add(columnSparkline3);
            groupColumn.Add(columnSparkline4);
            sv.SparklineContainer.Add(groupColumn);
            //4. Register Sparkline group with SheetVie

            //1.Create a Line Sparkline
            ExcelSparkline lineSparkline0 = new ExcelSparkline(1, 5, "Sheet1!B2:D2");
            ExcelSparkline lineSparkline1 = new ExcelSparkline(2, 5, "Sheet1!B3:D3");
            ExcelSparkline lineSparkline2 = new ExcelSparkline(3, 5, "Sheet1!B4:D4");
            ExcelSparkline lineSparkline3 = new ExcelSparkline(4, 5, "Sheet1!B5:D5");
            ExcelSparkline lineSparkline4 = new ExcelSparkline(5, 5, "Sheet1!B6:D6");

            //2. Create a Sparkline Group
            ExcelSparklineGroup groupLine = new ExcelSparklineGroup(setting1, SparklineType.Line);
            //3. Add Sparkline to group
            groupLine.Add(lineSparkline0);
            groupLine.Add(lineSparkline1);
            groupLine.Add(lineSparkline2);
            groupLine.Add(lineSparkline3);
            groupLine.Add(lineSparkline4);
            //4. Register Sparkline group with SheetView Sparkline container object
            sv.SparklineContainer.Add(groupLine);

            this.FpSpread1.Sheets[0].Rows[0].Font.Bold = true;
            this.FpSpread1.Sheets[0].Rows[10].Font.Bold = true;
            this.FpSpread1.Sheets[0].Rows[20].Font.Bold = true;
            this.FpSpread1.Sheets[0].Rows[30].Font.Bold = true;


        }

        private void SetChart()
        {


            OleDbConnection dbConnection = new OleDbConnection(connection);
            if (dbConnection == null)
                return;
            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }

            //按区域分配
            string delStr = string.Format("select SUM(数量) from SalesData where 区域='华东区' and 日期 between #2013-01-01# and #2014-01-01#");
            OleDbCommand command = new OleDbCommand(delStr, dbConnection);
            double eastCount = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 区域='西部区' and 日期 between #2013-01-01# and #2014-01-01#";
            double westCount = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 区域='南方区' and 日期 between #2013-01-01# and #2014-01-01#";
            double southCount = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 区域='北方区' and 日期 between #2013-01-01# and #2014-01-01#";
            double northCount = (double)command.ExecuteScalar();

            //按产品分配
            command.CommandText = "select SUM(数量) from SalesData where 产品='Spread' and 日期 between #2013-01-01# and #2014-01-01#";
            double spread = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 产品='ComponentOne Studio' and 日期 between #2013-01-01# and #2014-01-01#";
            double c1 = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 产品='ActiveReports' and 日期 between #2013-01-01# and #2014-01-01#";
            double ar = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 产品='MultiRow' and 日期 between #2013-01-01# and #2014-01-01#";
            double mul = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 产品='ActiveAnalysis' and 日期 between #2013-01-01# and #2014-01-01#";
            double aa = (double)command.ExecuteScalar();

            //按销售经理统计
            command.CommandText = "select SUM(数量) from SalesData where 销售经理='杨东波' and 日期 between #2013-01-01# and #2014-01-01#";
            double ydb = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 销售经理='蒋国华' and 日期 between #2013-01-01# and #2014-01-01#";
            double jgh = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 销售经理='曹骁男' and 日期 between #2013-01-01# and #2014-01-01#";
            double cxn = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 销售经理='卫金博' and 日期 between #2013-01-01# and #2014-01-01#";
            double wjb = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 销售经理='金宏强' and 日期 between #2013-01-01# and #2014-01-01#";
            double jhq = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 销售经理='朱玉辉' and 日期 between #2013-01-01# and #2014-01-01#";
            double zyh = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 销售经理='苏礼杰' and 日期 between #2013-01-01# and #2014-01-01#";
            double slj = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 销售经理='张洪军' and 日期 between #2013-01-01# and #2014-01-01#";
            double zhj = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 销售经理='孙娜娜' and 日期 between #2013-01-01# and #2014-01-01#";
            double snn = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 销售经理='韩丽娟' and 日期 between #2013-01-01# and #2014-01-01#";
            double hlj = (double)command.ExecuteScalar();

            command.CommandText = "select SUM(数量) from SalesData where 销售经理='严明磊' and 日期 between #2013-01-01# and #2014-01-01#";
            double yml = (double)command.ExecuteScalar();

            dbConnection.Close();

            this.FpSpread1.Sheets[0].Cells[10, 0].Text = "2013 年区域销售图";
            this.FpSpread1.Sheets[0].Cells[10, 0].HorizontalAlign = HorizontalAlign.Center;
            this.FpSpread1.Sheets[0].Cells[11, 0].Text = "区域";
            this.FpSpread1.Sheets[0].Cells[12, 0].Text = "华东区";
            this.FpSpread1.Sheets[0].Cells[13, 0].Text = "西部区";
            this.FpSpread1.Sheets[0].Cells[14, 0].Text = "南方区";
            this.FpSpread1.Sheets[0].Cells[15, 0].Text = "北方区";
            this.FpSpread1.Sheets[0].Cells[12, 1].Value = eastCount;
            this.FpSpread1.Sheets[0].Cells[13, 1].Value = westCount;
            this.FpSpread1.Sheets[0].Cells[14, 1].Value = southCount;
            this.FpSpread1.Sheets[0].Cells[15, 1].Value = northCount;
            this.FpSpread1.Sheets[0].Cells[11, 1].Text = "数量";

            this.FpSpread1.Sheets[0].Cells[20, 0].Text = "2013 年产品销售图";
            this.FpSpread1.Sheets[0].Cells[20, 0].HorizontalAlign = HorizontalAlign.Center;
            this.FpSpread1.Sheets[0].Cells[21, 0].Text = "产品";
            this.FpSpread1.Sheets[0].Cells[22, 0].Text = "Spread";
            this.FpSpread1.Sheets[0].Cells[23, 0].Text = "ComponentOne Studio";
            this.FpSpread1.Sheets[0].Cells[24, 0].Text = "ActiveReports";
            this.FpSpread1.Sheets[0].Cells[25, 0].Text = "MultiRow";
            this.FpSpread1.Sheets[0].Cells[26, 0].Text = "ActiveAnalysis";
            this.FpSpread1.Sheets[0].Cells[22, 1].Value = spread;
            this.FpSpread1.Sheets[0].Cells[23, 1].Value = c1;
            this.FpSpread1.Sheets[0].Cells[24, 1].Value = ar;
            this.FpSpread1.Sheets[0].Cells[25, 1].Value = mul;
            this.FpSpread1.Sheets[0].Cells[26, 1].Value = aa;
            this.FpSpread1.Sheets[0].Cells[21, 1].Text = "数量";


            this.FpSpread1.Sheets[0].Cells[30, 0].Text = "2013 年销售经理业绩图";
            this.FpSpread1.Sheets[0].Cells[30, 0].HorizontalAlign = HorizontalAlign.Center;
            this.FpSpread1.Sheets[0].Cells[31, 0].Text = "销售经理";
            this.FpSpread1.Sheets[0].Cells[31, 1].Text = "数量";
            this.FpSpread1.Sheets[0].Cells[32, 0].Text = "杨东波";
            this.FpSpread1.Sheets[0].Cells[33, 0].Text = "蒋国华";
            this.FpSpread1.Sheets[0].Cells[34, 0].Text = "曹骁男";
            this.FpSpread1.Sheets[0].Cells[35, 0].Text = "卫金博";
            this.FpSpread1.Sheets[0].Cells[36, 0].Text = "金宏强";
            this.FpSpread1.Sheets[0].Cells[37, 0].Text = "朱玉辉";
            this.FpSpread1.Sheets[0].Cells[38, 0].Text = "苏礼杰";
            this.FpSpread1.Sheets[0].Cells[39, 0].Text = "张洪军";
            this.FpSpread1.Sheets[0].Cells[40, 0].Text = "孙娜娜";
            this.FpSpread1.Sheets[0].Cells[41, 0].Text = "韩丽娟";
            this.FpSpread1.Sheets[0].Cells[42, 0].Text = "严明磊";

            this.FpSpread1.Sheets[0].Cells[32, 1].Value = ydb;
            this.FpSpread1.Sheets[0].Cells[33, 1].Value = jgh;
            this.FpSpread1.Sheets[0].Cells[34, 1].Value = cxn;
            this.FpSpread1.Sheets[0].Cells[35, 1].Value = wjb;
            this.FpSpread1.Sheets[0].Cells[36, 1].Value = jhq;
            this.FpSpread1.Sheets[0].Cells[37, 1].Value = zyh;
            this.FpSpread1.Sheets[0].Cells[38, 1].Value = slj;
            this.FpSpread1.Sheets[0].Cells[39, 1].Value = zhj;
            this.FpSpread1.Sheets[0].Cells[40, 1].Value = snn;
            this.FpSpread1.Sheets[0].Cells[41, 1].Value = hlj;
            this.FpSpread1.Sheets[0].Cells[42, 1].Value = yml;

            this.FpSpread1.Sheets[0].Cells[21, 1].Text = "数量";

            DataTable dtRegion = new DataTable();
            dtRegion.Columns.Add(new DataColumn("区域"));
            dtRegion.Columns.Add(new DataColumn("数量"));
            dtRegion.Rows.Add("华东区", eastCount);
            dtRegion.Rows.Add("西部区", westCount);
            dtRegion.Rows.Add("南方区", southCount);
            dtRegion.Rows.Add("北方区", northCount);

            //设置图表标签
            LabelArea label1 = new LabelArea();
            label1.Text = "2013 年区域销售图";
            label1.Location = new PointF(0.5f, 0.02f);
            label1.AlignmentX = 0.5f;
            label1.AlignmentY = 0.0f;

            LabelArea label2 = new LabelArea();
            label2.Text = "2013 年产品销售图";
            label2.Location = new PointF(0.5f, 0.02f);
            label2.AlignmentX = 0.5f;
            label2.AlignmentY = 0.0f;

            LabelArea label3 = new LabelArea();
            label3.Text = "2013 年销售经理业绩图";
            label3.Location = new PointF(0.5f, 0.02f);
            label3.AlignmentX = 0.5f;
            label3.AlignmentY = 0.0f;


            this.FpSpread1.Sheets[0].AddChart(new FarPoint.Web.Spread.Model.CellRange(11, 0, 5, 2), typeof(FarPoint.Web.Chart.BarSeries), 688, 252, 277, 323, ChartViewType.View3D, true);
            BarSeries s1 = (BarSeries)this.FpSpread1.Sheets[0].Charts[0].Model.PlotAreas[0].Series[0];
            this.FpSpread1.Sheets[0].Charts[0].Model.LabelAreas.RemoveAt(0);
            this.FpSpread1.Sheets[0].Charts[0].Model.LabelAreas.Add(label1);
            s1.VaryColors = true;
            s1.LabelVisible = true;

            this.FpSpread1.Sheets[0].AddChart(new FarPoint.Web.Spread.Model.CellRange(21, 0, 6, 2), typeof(FarPoint.Web.Chart.PieSeries), 688, 263, 277, 631, ChartViewType.View3D, true);
            PieSeries s2 = (PieSeries)this.FpSpread1.Sheets[0].Charts[1].Model.PlotAreas[0].Series[0];
            this.FpSpread1.Sheets[0].Charts[1].Model.LabelAreas.RemoveAt(0);
            this.FpSpread1.Sheets[0].Charts[1].Model.LabelAreas.Add(label2);
            s2.VaryColors = true;
            s2.LabelVisible = true;

            this.FpSpread1.Sheets[0].AddChart(new FarPoint.Web.Spread.Model.CellRange(31, 0, 12, 2), typeof(FarPoint.Web.Chart.RadarLineSeries), 688, 364, 277, 954);
            RadarLineSeries s3 = (RadarLineSeries)this.FpSpread1.Sheets[0].Charts[2].Model.PlotAreas[0].Series[0];
            this.FpSpread1.Sheets[0].Charts[2].Model.LabelAreas.RemoveAt(0);
            this.FpSpread1.Sheets[0].Charts[2].Model.LabelAreas.Add(label3);
            s3.VaryColors = true;
            s3.LabelVisible = true;

            for (int i = 0; i < this.FpSpread1.Sheets[0].RowCount; i++)
            {
                this.FpSpread1.Sheets[0].Rows[i].Height = 30;
            }
            for (int i = 0; i < 2; i++)
            {
                this.FpSpread1.Sheets[0].Columns[i].Width = 138;
            }

            this.FpSpread1.Sheets[0].AddSpanCell(10, 0, 1, 2);
            this.FpSpread1.Sheets[0].AddSpanCell(20, 0, 1, 2);
            this.FpSpread1.Sheets[0].AddSpanCell(30, 0, 1, 2);


        }

        internal SheetView ActiveSheet
        {
            get { return FpSpread1.ActiveSheetView; }
        }
    }
}